package com.hjw.weixun.Template;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hjw.weixun.db.JdbcUtil;
/**
 * 
 * @author henry_chen
 *date 2011-8-2
 * 封装jdbc 
 * @param <T>
 */
public  class JdbcTemplate<T extends Object> {
	private static final  Logger logger = LoggerFactory.getLogger(JdbcTemplate.class);
	private static int commit = 100; // 每100条记录一提交
	private static boolean hasNext = false;
	private DataSource dataSource;

	/**
	 * 注入数据源
	 * 
	 * @param dataSource
	 */
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	private DataSource getDataSource() {
		return dataSource;
	}

	/**
	 * 获得接口中泛型的实例
	 * 
	 * @param index
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private Class getGenericType(int index) {
		Type genType = getClass().getGenericSuperclass();
		if (!(genType instanceof ParameterizedType)) {
			return Object.class;
		}
		Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
		if (index >= params.length || index < 0) {
			throw new RuntimeException("Index outof bounds");
		}
		if (!(params[index] instanceof Class)) {
			return Object.class;
		}
		return (Class) params[index];
	}

	/**
	 * 得到Connection
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException {
		Connection connection = getDataSource() == null ? null: getDataSource().getConnection();
		return connection == null ? JdbcUtil.getInstance().getConnection(): connection;
	}

	/**
	 * 根据泛型T实体的给出的字段、查询语句、参数得到一个有数据的T实体对象
	 * 注：bean的字段名称必须与数据库查询结果列名一致
	 * 
	 * @param sql 查询语句
	 * @param args 参数
	 * @return T
	 * @throws Exception
	 */
	public T uniqueResult(String sql,Object... args) {
		return this.uniqueResult(null, null, sql, args);
	}
	
	/**
	 * 根据泛型T实体的给出的字段、查询语句、参数得到一个有数据的T实体对象
	 * 
	 * @param fieldNames 要设置值的字段
	 * @param sql 查询语句
	 * @param args 参数
	 * @return T
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public T uniqueResult(String[] fieldNames, String[] rsClums, String sql,Object... args) {
		sqlValidate(sql);
		Object obj = null;
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			logger.info(pstat.toString());
			rs = pstat.executeQuery();
			if(fieldNames==null||rsClums==null){
				obj = convertObject(rs);
			}else{
				obj = convertObject(fieldNames, rsClums, rs);
			}
			pstat.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(rs, pstat, conn);
		}
		return (T) obj;
	}

	/**
	 * 执行SQL语句方法,添加，修改，删除。没有 预编译
	 * 
	 * @param sql
	 * @return
	 */
	public int execute(String sql, Object... args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		boolean flag = false;
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			logger.info(pstat.toString());
			flag = pstat.execute();
			ResultSet rs = pstat.getGeneratedKeys();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(null, pstat, conn);
		}
		return -1;
	}

	/**
	 * 执行SQL查询语句方法，返回结果集对象
	 * 
	 * @param sql
	 * @return
	 */
	public ResultSet executeQuery(String sql, Object... args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			rs = pstat.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(null, pstat, conn);
		}
		return rs;
	}

	/**
	 * 功能描述：[新增、修改、删除]操作
	 * 
	 * @param sql
	 *            sql语句
	 * @param args
	 *            参数
	 * @return int 成功返回1 否则返回0
	 */
	public int executeUpdate(String sql, Object... args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		int r = 0;
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			r = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(null, pstat, conn);
		}
		return r;
	}

	/**
	 * 功能描述：批量[新增、修改、删除]操作；默认100条记录提交一次
	 * 
	 * @param sql
	 *            批量插入操作sql语句
	 * @param args
	 *            参数
	 * @return List<String> 操作成功后的标识集合
	 */
	public boolean executeBatch(String sql, Object[][] args) {
		return executeBatch(commit, sql, args);
	}

	/**
	 * 功能描述：批量[新增、修改、删除]操作
	 * 
	 * @param commit
	 *            每多少条记录提交一次的参数 默认：100
	 * @param sql
	 *            批量插入操作sql语句
	 * @param args
	 *            参数
	 * @return List<String> 操作成功后的标识集合
	 */
	public boolean executeBatch(int commit, String sql, Object[][] args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		boolean flag = false;
		try {
			// 1. 建立与数据库的连接
			conn = getConnection();
			conn.setAutoCommit(false);
			pstat = conn.prepareStatement(sql);
			commBatchExcute(commit, sql, args, conn, pstat);
			flag = true;
		} catch (Exception e) {
			flag = false;
			commRollback(conn);
			e.printStackTrace();
		} finally {
			JdbcUtil.free(null, pstat, conn);
		}
		return flag;
	}

	/**
	 * 回滚操作
	 * 
	 * @param conn
	 */
	private void commRollback(Connection conn) {
		// 若出现异常，对数据库中所有已完成的操作全部撤销，则回滚到事务开始状态
		try {
			if (!conn.isClosed()) {
				conn.rollback();// 4,当异常发生执行catch中SQLException时，记得要rollback(回滚)；
				System.out.println("批量执行失败，事务回滚！");
				conn.setAutoCommit(true);
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}

	/**
	 * 批量任务 核心 方法
	 * 
	 * @param commit
	 * @param sql
	 * @param args
	 * @param conn
	 * @param pstat
	 * @throws Exception
	 */
	private void commBatchExcute(int commit, String sql, Object[][] args,
			Connection conn, PreparedStatement pstat) throws Exception {
		int rowsTmp = 0;
		for (int i = 0; i < args.length; i++) {
			Object[] temp = args[i];
			for (int t = 1; t <= temp.length; t++) {
				Object value = temp[t - 1];
				pstat.setObject(t, value);
			}
			// 将上面的参数添加到此 PreparedStatement 对象的批处理命令中。
			pstat.addBatch();
			if (rowsTmp % commit == 0) {// 每500条记录一提交
				pstat.executeBatch();
				conn.commit();
				if (null == conn) { // 如果连接关闭了 就在创建一个 为什么要这样 原因是
					// conn.commit()后可能conn被关闭
					conn = getConnection();
					conn.setAutoCommit(false);
				}
			}
			rowsTmp++;
		}
		// 将一批参数提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。
		pstat.executeBatch();
		conn.commit();// 2,进行手动提交（commit）
		conn.setAutoCommit(true);// 3,提交完成后回复现场将Auto commit,还原为true,
	}

	/**
	 * 功能描述：查询一行记录，返回以HashMap为一行记录
	 * 
	 * @param sql
	 *            用于查询的sql语句
	 * @param args
	 *            sql预编译的参数
	 * @return HashMap<String,Object> 结果集
	 */
	public HashMap<String, Object> executeQueryToMap(String sql, Object... args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		HashMap<String, Object> result = new HashMap<String, Object>(0);
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			rs = pstat.executeQuery();
			ResultSetMetaData rsm = pstat.getMetaData();
			while (rs.next()) {
				for (int i = 1; i <= rsm.getColumnCount(); i++) {
					Object value = rs.getObject(i);
					result.put(rsm.getColumnName(i), value);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(rs, pstat, conn);
		}
		return result;
	}

	/**
	 * 
	 * 功能描述：查询全部结果，返回以HashMap为一行记录结果的List集合
	 * 
	 * @param sql
	 *            用于查询的sql语句
	 * @param args
	 *            sql预编译的参数
	 * @return List<HashMap<String,Object>> 结果集
	 */
	public List<HashMap<String, Object>> executeQueryAllToMap(String sql,
			Object... args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>(
				0);
		HashMap<String, Object> temp = null;
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			rs = pstat.executeQuery();
			ResultSetMetaData rsm = pstat.getMetaData();
			while (rs.next()) {
				temp = new HashMap<String, Object>();
				for (int i = 1; i <= rsm.getColumnCount(); i++) {
					Object value = rs.getObject(i);
					temp.put(rsm.getColumnName(i), value);
				}
				result.add(temp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(rs, pstat, conn);
		}
		return result;
	}
	
	/**
	 * 
	 * 
	 * 功能描述：查询全部结果，返回泛型中最终指明的对象的结果List集合
	 * 
	 * @param sql用于查询的sql语句
	 * @param args sql预编译的参数
	 * @return List<T> 结果集
	 * 
	 */
	public List<T> executeQueryAllToList(String sql, Object... args) {
		return this.executeQueryAllToList(null,null,sql, args);
	}

	/**
	 * 
	 * 
	 * 功能描述：查询全部结果，返回泛型中最终指明的对象的结果List集合
	 * 
	 * @param sql
	 *            用于查询的sql语句
	 * @param obj
	 *            要设置值的对象
	 * @param toSetFirlds
	 *            要设置值的字段
	 * @param toGetClums
	 *            要从ResultSet中取结果的列表 toGetClums必须与toSetFirlds参数按顺序一一对应，否则会出现数据错误
	 * @param args
	 *            sql预编译的参数
	 * @return List<T> 结果集
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<T> executeQueryAllToList(String[] beanFields, String[] rsClums,String sql, Object... args) {
		sqlValidate(sql);
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<T> result = new ArrayList<T>(0);
		try {
			conn = getConnection();
			pstat = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++) {
				pstat.setObject(i, args[i - 1]);
			}
			logger.info(pstat.toString());
			rs = pstat.executeQuery();
			while (rs.next()) {
				Object obj = null;
				//rs.previous();
				if(beanFields==null||rsClums==null){
					rs.previous();
					obj = convertObject(rs);
				}else{
					obj = convertObject(beanFields, rsClums, rs);
				}
				result.add((T) obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.free(rs, pstat, conn);
		}
		return result;
	}

	/**
	 * 将结果集中数据 转换到 bean 中
	 * 
	 * @param beanFields
	 *            bean要赋值的字段
	 * @param rsClums
	 *            要取值的列名
	 * @param rs
	 *            ResultSet 结果集
	 * @return
	 * @throws Exception
	 */
	private Object convertObject(String[] beanFields, String[] rsClums,ResultSet rs) throws Exception {
		Field[] fields;
		Object obj = getGenericType(0) != null ? (getGenericType(0).newInstance()) : null;
		if (obj != null) {
			String[] colNames = getColNames(rs);
			try {
				fields = obj.getClass().getDeclaredFields();
				int cl = beanFields.length > rsClums.length ? rsClums.length
						: beanFields.length;
				for (int j = 0; j < cl; j++) {
					String clumName = colNames[j];// 该列的列名
					Object clumValue = rs.getObject(j + 1);// 该列的值
					String toGetClumName = rsClums[j];// 要从结果取出要取的列名
					String fieldName = beanFields[j];// 要给对象设置字的字段名
					String methodName = "set"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1, fieldName.length());
					if (clumName.equals(toGetClumName)) {
						for (Field field : fields) {
							field.setAccessible(true); // 设置些属性是可以访问的
							if (field.getName().equals(fieldName)) {
								Method m = obj.getClass().getMethod(methodName,clumValue == null ? null : clumValue.getClass());
								m.invoke(obj, clumValue);// 调用obj对象的这个方法
								break;
							}
						}
					}
				}
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return obj;
	}

	/**
	 * 列表列名与bean对象字段名称相同的时候使用
	 * @param rs
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private Object convertObject(ResultSet rs) {
		Object object = null;
		try {
			Class clazz = getGenericType(0);
			String[] colNames;
			colNames = getColNames(rs);
			// 得到所有的列名
			Method[] ms = clazz.getMethods();// 得到javaBean中所有set或get方法
			if (rs.next()) {
				object = clazz.newInstance();
				for (int i = 0; i < colNames.length; i++) {
					String colName = colNames[i];
					String methodName = "set"+ colName.substring(0, 1).toUpperCase()+ colName.substring(1, colName.length());
					for (Method m : ms) {
						if (methodName.equals(m.getName())) {
							m.invoke(object, rs.getObject(colName));// 调用set方法,将该列的值放到set方法当中去.
							break;
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return object;
	}

	private void sqlValidate(String sql) {
		if (sql == null || sql.trim().length() < 0) {
			throw new RuntimeException("sql sentence is null or is '' ");
		}
	}

	/**
	 * 从ResultSet中的到查询结果的列
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private static String[] getColNames(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		String[] colNames = new String[count];
		for (int i = 1; i <= count; i++) {
			colNames[i - 1] = rsmd.getColumnLabel(i);
		}
		return colNames;
	}
	/**
	 * 将rs的结果集转化为对象 
	 * @param rs
	 * @param clazz
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	
	public static List populate(ResultSet rs , Class clazz) throws SQLException, InstantiationException, IllegalAccessException{ 
		  	//结果集的元素对象    
		        ResultSetMetaData rsmd = rs.getMetaData();   
		        //获取结果集的元素个数   
		         int colCount = rsmd.getColumnCount();   
		         //返回结果的列表集合   
		         List list = new ArrayList();   
		         //业务对象的属性数组   
		         Field[] fields = clazz.getDeclaredFields();   
		         while(rs.next()){//对每一条记录进行操作   
		            Object obj = clazz.newInstance();//构造业务对象实体   
		             //将每一个字段取出进行赋值   
		             for(int i = 1;i<=colCount;i++){   
		                 Object value = rs.getObject(i);   
		                 //寻找该列对应的对象属性   
		                 for(int j=0;j<fields.length;j++){   
		                    Field f = fields[j];   
		                     //如果匹配进行赋值   
		                     if(f.getName().equalsIgnoreCase(rsmd.getColumnName(i))){   
		                         boolean flag = f.isAccessible();   
		                         f.setAccessible(true);   
		                         f.set(obj, value);   
		                         f.setAccessible(flag);   
		                    }   
		                 }   
		             }   
		            list.add(obj);   
		         }   
		        return list;   
		    }   
}
